﻿''' <summary>
''' Implementation of INlsProvider which loads NLS entries from the database.
''' </summary>
''' <remarks></remarks>
Public Class SqlCeTranslationProvider
    Implements ITranslationProvider

    Private _connectionString As String
    Private _sqlTableName As String
    Private _sqlKeyColumn As String
    Private _sqlValueColumn As String
    Private _sqlLanguageIDColumn As String
    Private _sqlUserDefinedColumn As String

    ''' <summary>
    ''' Initializes a new instance of the <see cref="SqlCeTranslationProvider" /> class.
    ''' </summary>
    ''' <param name="connectionString">The connection string.</param>
    Sub New(connectionString As String)
        Me.New(connectionString, sqlTableName:="Translations", sqlKeyColumn:="Key", sqlValueColumn:="Text", sqlLanguageIDColumn:="LanguageID", sqlUserDefinedColumn:="UserDefined")
    End Sub

    Sub New(connectionString As String, sqlTableName As String, sqlKeyColumn As String, sqlValueColumn As String, sqlLanguageIDColumn As String, sqlUserDefinedColumn As String)

        If (String.IsNullOrEmpty(connectionString)) Then
            Throw New ArgumentNullException("connectionString")
        End If
        _connectionString = connectionString
        _sqlTableName = sqlTableName
        _sqlKeyColumn = sqlKeyColumn
        _sqlValueColumn = sqlValueColumn
        _sqlLanguageIDColumn = sqlLanguageIDColumn
        _sqlUserDefinedColumn = sqlUserDefinedColumn
    End Sub

    ''' <summary>
    ''' Loads the NLS values for the given set of keys.
    ''' </summary>
    ''' <param name="languageID">The language ID.</param>
    ''' <param name="keys">The keys.</param>
    ''' <returns></returns>
    Public Overridable Function LoadNls(languageID As Integer, ParamArray keys() As String) As System.Collections.Generic.IDictionary(Of String, String) Implements ITranslationProvider.LoadTranslations
        Using con As New SqlServerCe.SqlCeConnection(_connectionString)
            con.Open()
            Using reader = GetReader(con, languageID, keys)
                Return LoadNls(reader)
            End Using
        End Using
    End Function


    ''' <summary>
    ''' Loads the NLS values for all keys which match the given filter.
    ''' Filter may accept wildcards '*' or '%'.
    ''' </summary>
    ''' <param name="languageID">The language ID.</param>
    ''' <param name="keyFilter">The key filter.</param>
    ''' <returns></returns>
    Public Overridable Function LoadNlsByFilter(languageID As Integer, keyFilter As String) As System.Collections.Generic.IDictionary(Of String, String) Implements ITranslationProvider.LoadTranslationsByFilter
        Using con As New SqlServerCe.SqlCeConnection(_connectionString)
            con.Open()
            Using reader = GetReaderWithFilter(con, languageID, keyFilter)
                Return LoadNls(reader)
            End Using
        End Using
    End Function

    Private Function GetReader(con As SqlServerCe.SqlCeConnection, languageID As Integer, ParamArray keys() As String) As SqlServerCe.SqlCeDataReader
        Dim cmd = con.CreateCommand()
        Dim sqlNlsKeys = keys.Select(Function(k) "'" + k.Replace("'", "''") + "'").Aggregate(Function(cur, nxt) cur + ", " + nxt)
        cmd.CommandText = "SELECT [" & _sqlKeyColumn & "], [" & _sqlValueColumn & "] FROM [" & _sqlTableName & "] WHERE [" & _sqlLanguageIDColumn & "] = @languageID AND [" & _sqlKeyColumn & "] IN (" & sqlNlsKeys & ")"
        cmd.Parameters.Add("languageID", SqlDbType.Int).Value = languageID
        Return cmd.ExecuteReader()
    End Function

    Private Function GetReaderWithFilter(con As SqlServerCe.SqlCeConnection, languageID As Integer, keyFilter As String) As SqlServerCe.SqlCeDataReader
        Dim cmd = con.CreateCommand()
        cmd.CommandText = "SELECT [" & _sqlKeyColumn & "], [" & _sqlValueColumn & "] FROM [" & _sqlTableName & "] WHERE [" & _sqlLanguageIDColumn & "] = @languageID AND [" & _sqlKeyColumn & "] LIKE '" & keyFilter.Replace("'", "''").Replace("*", "%") & "'"
        cmd.Parameters.Add("languageID", SqlDbType.Int).Value = languageID
        Return cmd.ExecuteReader()
    End Function


    Private Function LoadNls(reader As SqlServerCe.SqlCeDataReader) As System.Collections.Generic.IDictionary(Of String, String)
        Dim values As New Dictionary(Of String, String)
        While reader.Read()
            values(reader.GetString(0)) = If(reader.IsDBNull(1), Nothing, reader.GetString(1))
        End While
        Return values
    End Function

    Public Overridable Sub UpdateNls(languageID As Integer, key As String, newValue As String) Implements ITranslationProvider.UpdateTranslation
        Using con As New SqlServerCe.SqlCeConnection(_connectionString)
            con.Open()
            Dim cmd = con.CreateCommand()
            cmd.CommandText = "UPDATE [" & _sqlTableName & "] SET [" & _sqlValueColumn & "] = @value, [" & _sqlUserDefinedColumn & "] = 1 WHERE [" & _sqlLanguageIDColumn & "] = @languageID AND [" & _sqlKeyColumn & "] = @key"
            cmd.Parameters.Add("value", SqlDbType.NVarChar).Value = newValue
            cmd.Parameters.Add("languageID", SqlDbType.Int).Value = languageID
            cmd.Parameters.Add("key", SqlDbType.NVarChar).Value = key
            If cmd.ExecuteNonQuery() = 0 Then
                cmd.CommandText = "INSERT INTO [" & _sqlTableName & "]([" & _sqlKeyColumn & "], [" & _sqlValueColumn & "], [" & _sqlLanguageIDColumn & "], [" & _sqlUserDefinedColumn & "]) VALUES(@key, @value, @languageID, 1)"
                cmd.ExecuteNonQuery()
            End If
        End Using
    End Sub


End Class
